﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    //封装SQLite常用的方法：ExecuteReader、ExecuteScalar、ExecuteNonQuery、ExecuteFill
    public static class SQLiteHelper
    {
        private static string connStr = "Data Source = RMS.db";

        public static SQLiteDataReader ExecuteReader(out SQLiteConnection sqlconnection, string sql, params SQLiteParameter[] sQLiteParameters)
        {
            //连接数据库
            //特殊情况：因dataReader需要在有连接的情况下才能读取数据，因此在读取完成之前需要保持连接
            SQLiteConnection sqliteconnection = new SQLiteConnection(connStr);
            //开启连接
            sqliteconnection.Open();
            sqlconnection = sqliteconnection;

            //创建SQl指令
            using (SQLiteCommand sQLiteCommand = new SQLiteCommand(sql, sqliteconnection))
            {
                sQLiteCommand.Parameters.AddRange(sQLiteParameters);

                return sQLiteCommand.ExecuteReader();
            }



        }


        public static Object ExecuteScalar(string sql, params SQLiteParameter[] sQLiteParameters)
        {
            //连接数据库


            using (SQLiteConnection sqliteconnection = new SQLiteConnection(connStr))
            {
                //开启连接
                sqliteconnection.Open();

                //创建SQl指令
                using (SQLiteCommand sQLiteCommand = new SQLiteCommand(sql, sqliteconnection))
                {
                    sQLiteCommand.Parameters.AddRange(sQLiteParameters);

                    return sQLiteCommand.ExecuteScalar();
                }

            }

        }


        public static int ExecuteNonQuery(string sql, params SQLiteParameter[] sQLiteParameters)
        {
            //连接数据库


            using (SQLiteConnection sqliteconnection = new SQLiteConnection(connStr))
            {
                //开启连接
                sqliteconnection.Open();

                //创建SQl指令
                using (SQLiteCommand sQLiteCommand = new SQLiteCommand(sql, sqliteconnection))
                {
                    sQLiteCommand.Parameters.AddRange(sQLiteParameters);

                    return sQLiteCommand.ExecuteNonQuery();
                }

            }
        }

        public static DataTable ExecuteFill(string sql, params SQLiteParameter[] sQLiteParameters)
        {
            //连接数据库
            DataTable dt = new DataTable();

            using (SQLiteConnection sqliteconnection = new SQLiteConnection(connStr))
            {
                //开启连接
                sqliteconnection.Open();

                //创建SQl指令
                using (SQLiteDataAdapter sQLiteDataAdapter = new SQLiteDataAdapter(sql, sqliteconnection))
                {
                    sQLiteDataAdapter.SelectCommand.Parameters.AddRange(sQLiteParameters);
                    //填充数据表
                    sQLiteDataAdapter.Fill(dt);
                    return dt;
                }

            }
        }

    }
}
